package com.sibco.client.business;

import com.bstek.dorado.data.*;
import com.bstek.dorado.data.db.SqlDataset;
import com.bstek.dorado.common.*;
import com.sibco.client.common.CommUtil;
import com.sibco.client.common.DBTools;
import com.sibco.client.common.Tools;
import com.sibco.client.system.User;

/**
 * queryOughtReceive_oughtReceiveDatasetListener
 */
public class OughtReceiveQryListener extends AbstractDatasetListener {
  public boolean beforeLoadData(Dataset dataset)
     throws Exception {
    String pvdEmployeeId="",sql="";
  	
  	DoradoContext context = DoradoContext.getContext();
    Object obj = context.getAttribute(context.SESSION, "User");
    if (obj != null) {
    	User user = (User) obj;

    	//pvdEmployeeId=user.getPvdEmployeeId();
    	sql = "SELECT /*+all_rows*/ pvd.provider_name,cmp.company_code,cmp.COMPANY_CHINESE_NAME,cp.company_payer_name," +
    			"o.ought_receive_id,o.ought_month,pv.VERSION_ID,o.ought_amount,o.PAYMENT_VERSION_ID paymentVersionId," +
    			"o.PAYMENT_VERSION_ID,o.payment_type,to_char(o.closed_date,'yyyy-mm-dd') closed_date,pv.createtime,u.real_name " +
    			"FROM " +
    			"clt_fin_payment_version pv,clt_pb_company cmp,clt_fin_ought_receive o,clt_pb_company_payer cp,clt_pb_provider pvd,clt_pvd_user u " +
    			"WHERE " +
    			"o.PAYMENT_VERSION_ID = pv.PAYMENT_VERSION_ID AND cmp.company_id = o.company_id " +
    			"and pvd.provider_id=o.provider_id and cp.company_payer_id=o.company_payer_id and u.user_id(+)=pv.createman_id ";
//    			"AND exists " +
//    			"	(select 'x' from cs_contract_send send_1_1,rt_contract_businessman cbm,rt_pvd_emp_scope scp " +
//    			"      where send_1_1.company_id=o.company_id " +
//    			"      and send_1_1.payment_provider_id=o.provider_id and send_1_1.company_payer_id=o.company_payer_id " +
//    			"      and cbm.contract_send_id=send_1_1.contract_send_id " +
//    			"      and cbm.businessman_id=scp.fe_empid2" +
//    			"      and cbm.start_date<=sysdate  and (cbm.end_date is null or cbm.end_date>=sysdate) " +
//    			"	   and  scp.fe_empid1='"+pvdEmployeeId+"'  ";
//    	if (!Tools.toStr(dataset.parameters().getString("cs_man")).equals(""))  {//???
//        	sql = sql + " and send_1_1.cs_man = " + dataset.parameters().getString("cs_man")+"";
//        }
//    	sql = sql +")"; 


    	if (!Tools.toStr(dataset.parameters().getString("providerId")).equals(""))  {
        	sql = sql + " and o.provider_id = " + dataset.parameters().getString("providerId");
        }
    	if (!Tools.toStr(dataset.parameters().getString("companyPayerId")).equals(""))  {
    		sql = sql + " and o.company_payer_id = " + dataset.parameters().getString("companyPayerId");
    	}
    	if (!Tools.toStr(dataset.parameters().getString("companyId")).equals(""))  {
        	sql = sql + " and o.company_id = " + dataset.parameters().getString("companyId");
        }
    	if (!Tools.toStr(dataset.parameters().getString("oughtMonth1")).equals(""))  {
        	sql = sql + " and o.ought_month >= '" + dataset.parameters().getString("oughtMonth1")+"'";
        }
    	if (!Tools.toStr(dataset.parameters().getString("oughtMonth2")).equals(""))  {
            sql = sql + " and o.ought_month <= '" + dataset.parameters().getString("oughtMonth2")+"'";
        }
    	if (!Tools.toStr(dataset.parameters().getString("amount1")).equals("")
    		&& !Tools.toStr(dataset.parameters().getString("amount1")).equals("null")	)  {
            sql = sql + " and o.ought_amount >= " + dataset.parameters().getString("amount1");
        }
    	if (!Tools.toStr(dataset.parameters().getString("amount2")).equals("")
        	&& !Tools.toStr(dataset.parameters().getString("amount2")).equals("null")	)  {
            sql = sql + " and o.ought_amount <= " + dataset.parameters().getString("amount2");
        }
    	if (!Tools.toStr(dataset.parameters().getString("paymentType")).equals(""))  {
        	sql = sql + " and o.payment_type = " + dataset.parameters().getString("paymentType");
        }    	
    	String scope1=Tools.getSysdate("yyyyMMdd");
		scope1=Tools.monthCalculate(scope1, -11).substring(0,6);
		sql+=" and o.ought_month>='"+scope1+"' ";
		
    	sql += CommUtil.getRightSql(dataset,user,"cmp","");
    	sql = sql + " order by o.ought_month desc,cmp.COMPANY_CHINESE_NAME ";
    	SqlDataset sqlDataset=(SqlDataset)dataset;
    	sqlDataset.setSql(sql);
    }
  	
    return true;
  }

}
